# -*- coding: utf-8 -*-
"""
Python 连接 sqlserver 提取数据
"""
import pymssql
import pandas as pd


def df_into_mssql(conn,tb_name,df,types="insert"):
    '''将df导入mssql表.types指定导入之前是否清空表'''
    # 判断是否需要清空表
    cursor = conn.cursor()
    if types.upper() in "DELETE TRUNCATE" :
        cursor.execute("delete from "+tb_name)
        cursor.execute("commit")
    # 将df转成字符形式，填充缺失值
    # 处理空值问题，因为刚才转成字符串时，None被转成"None"
    df2 = df.applymap(lambda s:str(s))
    nan_df = df.notnull()
    df2=df2.where(nan_df, '')  # 对于原来是NaN的，要转成空字符串
    # 创建导数的sql
    sql = "insert into tb_name ( cols ) values ( th_value) "
    cols = ', '.join(list(df2.columns))
    num = len(list(df2.columns))
    num2 = ['%s' for i in range(num)]
    th_value = ', '.join(num2)
    sql = sql.replace('tb_name', tb_name)
    sql = sql.replace('cols', cols)
    sql = sql.replace('th_value', th_value)
    # 将df转成list-tuple格式，才能导入数据库
    param = []
    if len(df2)>1:
        for i in range(len(df2)):
            param.append(tuple(df2.iloc[i]))  # 转成list_tuple格式
    if len(df2)==1:
        param = [tuple(df2.iloc[0])]
    # 入库
    cursor.executemany(sql, prams)
    conn.commit()
    cursor.close()
    
    


def data_from_mssql(sql, host='192.168.1.250', user='sa', password='123456', database='pras'):
    '''建立到mssql的连接，然后查询数据，返回查询结果集，为list-tuple格式，后续需要处理成list-list格式'''
    # 建立链接，创建查询句柄
    conn = pymssql.connect(host=host, user=user, password=password, database=database)
    cur = conn.cursor()
    # 执行sql
    cur.execute(sql)
    # 导出数据
    data = cur.fetchall()
    data = mssql_list(data)
    # 关闭数据库链接
    cur.close()
    conn.close()
    return data


def astypes(value, types):
    '''将int转float，str转str'''
    if 'int' in types:  # 如果是整形，则转成浮点型
        return float(value)
    if 'obj' in types and value != None:  # 如果是字符型，且不是空值
        return str(value)
    return value  # 如果不是上面的情况，则直接返回值


def query_data(sql, host='192.168.1.250', user='sa', password='123456', database='pras'):
    '''建立到mssql的连接，然后查询数据，返回查询结果集，为df格式，直接处理成list-list格式.
    返回查询的数据和列名。
    使用了pandas的read_sql,这样才可以查看字段的数据类型
    '''
    conn = pymssql.connect(host=host, user=user, password=password, database=database)
    data = pd.read_sql(sql, con=conn)
    cols = [col.upper() for col in list(data.columns)]  # 获取列名
    dtype = [str(i) for i in list(data.dtypes)]  # 获取数据类型
    data2 = []
    for i in range(len(cols)):  # 对每一列处理
        sub = list(data[cols[i]].apply(astypes, types=dtype[i]))  # 将每一列转成list
        data2.append(sub)
    # 现在是每一列是list的一个元素，要转换成取取每列对应位置的元素构成一条记录
    # 也就是，从数据库取的是列，现在要改成取行
    data3 = [[data2[i][j] for j in range(len(cols))] for i in range(len(data2))]
    return data3

def mssql_list(fetch_data):
    '''将mssql查询出来的list_tuple数据转成list-list格式，这样方便后续程序接入处理。难点是ID字段的处理'''
    table = []
    for line in fetch_data:
        data = []
        idx = str(line[0])  # 对mssql查询出来的ID（带uid标识符）转成字符串
        data.append(idx)
        idx2 = list(line[1:])
        data2 = data + idx2

        table.append(data2)
    return table


def connect(host='192.168.1.250', user='sa', password='123456', database='pras'):
    '''建立到mssql的连接'''
    # 建立链接，创建查询句柄
    conn = pymssql.connect(host=host, user=user, password=password, database=database)
    return conn

if __name__ == "__main__":
    print("请从外部模块导入...")
